import type { KnowledgeDetail } from '@/types/knowledge';

/**
 * 存储过程与函数知识点数据
 */
export const procedureKnowledge: KnowledgeDetail[] = [
	{
		id: 1,
		title: '存储过程基础',
		description: '存储过程是一组预编译的SQL语句，可以接受参数并返回结果，提高代码复用性和性能。',
		icon: '💻',
		categoryId: 'sql-procedure',
		categoryName: '存储过程与函数',
		categoryIcon: '💻',
		difficulty: '高级',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #e74c3c 0%, #c0392b 100%)',
		keyPoints: [
			{
				point: '创建存储过程：CREATE PROCEDURE procedure_name (参数列表) BEGIN ... END。',
				code: `-- MySQL创建存储过程
DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

-- 调用存储过程
CALL GetUserById(1);

-- PostgreSQL创建存储过程
CREATE PROCEDURE GetUserById(IN user_id INT)
LANGUAGE SQL
AS $$
    SELECT * FROM users WHERE id = user_id;
$$;

-- 调用
CALL GetUserById(1);`
			},
			{
				point: '存储过程参数：IN（输入）、OUT（输出）、INOUT（输入输出）。',
				code: `-- 带IN参数的存储过程
CREATE PROCEDURE UpdateUserStatus(
    IN user_id INT,
    IN new_status VARCHAR(20)
)
BEGIN
    UPDATE users SET status = new_status WHERE id = user_id;
END;

-- 带OUT参数的存储过程
CREATE PROCEDURE GetUserCount(OUT total_count INT)
BEGIN
    SELECT COUNT(*) INTO total_count FROM users;
END;

-- 调用OUT参数
CALL GetUserCount(@count);
SELECT @count;

-- 带INOUT参数的存储过程
CREATE PROCEDURE IncrementCounter(INOUT counter INT)
BEGIN
    SET counter = counter + 1;
END;

SET @num = 10;
CALL IncrementCounter(@num);
SELECT @num;  -- 返回11`
			},
			{
				point: '删除存储过程：DROP PROCEDURE procedure_name。',
				code: `-- 删除存储过程
DROP PROCEDURE GetUserById;

-- 如果存在则删除
DROP PROCEDURE IF EXISTS GetUserById;

-- 查看所有存储过程
SHOW PROCEDURE STATUS;  -- MySQL
\\df  -- PostgreSQL`
			},
			{
				point: '存储过程的优点：提高性能、代码复用、安全性、事务控制。',
				code: `-- 存储过程的优点：
-- 1. 性能：预编译，执行更快
-- 2. 复用：一次定义，多次调用
-- 3. 安全：可以控制访问权限
-- 4. 事务：可以在过程中控制事务

-- 示例：转账存储过程
CREATE PROCEDURE TransferMoney(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
END;`
			},
			{
				point: '存储过程的流程控制：IF-ELSE、WHILE、LOOP等。',
				code: `-- IF-ELSE语句
CREATE PROCEDURE CheckUserStatus(IN user_id INT)
BEGIN
    DECLARE user_status VARCHAR(20);
    SELECT status INTO user_status FROM users WHERE id = user_id;
    
    IF user_status = 'active' THEN
        SELECT 'User is active' AS message;
    ELSEIF user_status = 'inactive' THEN
        SELECT 'User is inactive' AS message;
    ELSE
        SELECT 'Unknown status' AS message;
    END IF;
END;

-- WHILE循环
CREATE PROCEDURE CreateTestUsers(IN count INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= count DO
        INSERT INTO users (name) VALUES (CONCAT('User', i));
        SET i = i + 1;
    END WHILE;
END;`
			}
		]
	},
	{
		id: 2,
		title: '用户定义函数',
		description: '用户定义函数是可以返回值的存储程序，可以在SQL语句中像内置函数一样使用。',
		icon: '⚙️',
		categoryId: 'sql-procedure',
		categoryName: '存储过程与函数',
		categoryIcon: '💻',
		difficulty: '高级',
		color: 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)',
		headerBg: 'linear-gradient(135deg, #2c3e50 0%, #34495e 100%)',
		tagBg: 'linear-gradient(135deg, #3498db 0%, #2980b9 100%)',
		tagColor: '#ffffff',
		difficultyBg: 'linear-gradient(135deg, #e74c3c 0%, #c0392b 100%)',
		keyPoints: [
			{
				point: '创建函数：CREATE FUNCTION function_name(参数) RETURNS 类型 BEGIN ... END。',
				code: `-- MySQL创建函数
DELIMITER //
CREATE FUNCTION GetUserName(user_id INT)
RETURNS VARCHAR(100)
READS SQL DATA
BEGIN
    DECLARE user_name VARCHAR(100);
    SELECT name INTO user_name FROM users WHERE id = user_id;
    RETURN user_name;
END //
DELIMITER ;

-- 使用函数
SELECT GetUserName(1) AS user_name;

-- PostgreSQL创建函数
CREATE FUNCTION GetUserName(user_id INT)
RETURNS VARCHAR(100)
LANGUAGE SQL
AS $$
    SELECT name FROM users WHERE id = user_id;
$$;`
			},
			{
				point: '函数vs存储过程：函数必须有返回值，可以在SELECT中使用；存储过程可以没有返回值。',
				code: `-- 函数：必须有返回值
CREATE FUNCTION CalculateTotal(price DECIMAL, quantity INT)
RETURNS DECIMAL(10,2)
BEGIN
    RETURN price * quantity;
END;

-- 在SELECT中使用函数
SELECT 
    product_name,
    price,
    quantity,
    CalculateTotal(price, quantity) AS total
FROM order_items;

-- 存储过程：可以没有返回值
CREATE PROCEDURE UpdateUserStatus(IN user_id INT, IN status VARCHAR(20))
BEGIN
    UPDATE users SET status = status WHERE id = user_id;
END;

-- 只能通过CALL调用
CALL UpdateUserStatus(1, 'active');`
			},
			{
				point: '删除函数：DROP FUNCTION function_name。',
				code: `-- 删除函数
DROP FUNCTION GetUserName;

-- 如果存在则删除
DROP FUNCTION IF EXISTS GetUserName;

-- 查看所有函数
SHOW FUNCTION STATUS;  -- MySQL
\\df  -- PostgreSQL`
			},
			{
				point: '函数的特性：确定性、安全性等。',
				code: `-- DETERMINISTIC：对于相同输入总是返回相同结果
CREATE FUNCTION AddNumbers(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN a + b;
END;

-- NOT DETERMINISTIC：可能返回不同结果
CREATE FUNCTION GetCurrentTime()
RETURNS TIMESTAMP
NOT DETERMINISTIC
BEGIN
    RETURN CURRENT_TIMESTAMP;
END;

-- 特性说明：
-- DETERMINISTIC：可以缓存结果，提高性能
-- NOT DETERMINISTIC：每次调用都执行`
			},
			{
				point: '函数中的异常处理：可以使用异常处理机制。',
				code: `-- MySQL函数异常处理
CREATE FUNCTION SafeDivide(a DECIMAL, b DECIMAL)
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE result DECIMAL(10,2);
    
    IF b = 0 THEN
        RETURN NULL;  -- 或抛出异常
    END IF;
    
    SET result = a / b;
    RETURN result;
END;

-- 使用
SELECT SafeDivide(10, 2) AS result;      -- 返回5
SELECT SafeDivide(10, 0) AS result;       -- 返回NULL`
			}
		]
	}
];

